I get the error SQL Error [1525] [HY000]: Incorrect DATETIME value: '' when running following query:
SELECT * FROM mytable where date_time_column >=''
The query was working for the past few months and it started giving the above error since yesterday.
My MySQL version is 8.0.17.
I tried running the code via older versions of my code and still get the same error. Even trying it in MySQL Workbench gives the same error.
What Database config changes would have caused this?
It's seems not related to database changes itself, but something changed in version 8.0.16 regarding comparison on DATETIME and TIMESTAMP types against strings. The behavior is documented already in 8.0.16 change log as following:
When comparing
DATEvalues with constant strings, MySQL first tries to convert the string to aDATEand then to perform the comparison. When the conversion failed, MySQL executed the comparison treating theDATEas a string, which could lead to unpredictable behavior. Now in such cases, if the conversion of the string to aDATEfails, the comparison fails withER_WRONG_VALUE. (Bug #29025656)
Also, this is reported as a bug here.
PD: Your query works on my old 5.7 MySQL version.
For me the issue was that a TRIGGER would try to update that same field after every update to a row.
The trigger would not use the correct format when making a comparison.